package domain;

import util.JDBCUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Sales {
    private String name;
    private int buynum;

    public List<Sales> search(String year, String month) throws SQLException {
        String sql = "SELECT products.name,SUM(orderitem.buynum) totalsalnum "
                + "FROM orders,products,orderItem "
                + "WHERE orders.id=orderItem.order_id "
                + "AND products.id=orderItem.product_id AND orders.paystate=1 "
                + "AND year(ordertime)= " + year + " and month(ordertime)=" + month + " "
                + "GROUP BY products.name ORDER BY totalsalnum DESC";

        Statement statement = JDBCUtil.getConnection().createStatement();
        ResultSet res = statement.executeQuery(sql);
        List<Sales> salesList = new ArrayList<Sales>();
        while (res.next()) {
            Sales sales = new Sales();
            sales.setName(res.getString(1));
            sales.setBuynum(res.getInt(2));
            salesList.add(sales);
        }
        return salesList;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getBuynum() {
        return buynum;
    }

    public void setBuynum(int buynum) {
        this.buynum = buynum;
    }
}
